package com.zr.sms.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.sms.dao.GoodsDao;
import com.zr.sms.model.Goods;
import com.zr.sms.util.JDBCUtil;

/**
 * 对商品的增删改查
 * @author 吴浩 
 *
 */
public class GoodsDaoimpl implements   GoodsDao{
	
	public List<Goods> getGoods(int start, int pagesize) {
		// TODO Auto-generated method stub
		List<Goods> goods=new ArrayList<Goods>();
		Connection connection=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select * from goods limit ?,?");
		try {
			PreparedStatement pStatement=connection.prepareStatement(sql.toString());
			pStatement.setInt(1, start);
			pStatement.setInt(2, pagesize);
			ResultSet rs=pStatement.executeQuery();
			while(rs.next()){
				Goods gs=new Goods();
				gs.setGid(rs.getInt("gid"));
				gs.setGname(rs.getString("gname"));
				gs.setGinventory(rs.getShort("ginventory"));
				gs.setGprice(rs.getDouble("gprice"));
				gs.setGdate(rs.getString("gdate"));
				gs.setGquality(rs.getString("gquality"));
				gs.setGaddress(rs.getString("gaddress"));
				gs.setGclassify(rs.getString("gclassify"));
				gs.setGcost(rs.getDouble("gcost"));
				goods.add(gs);
			}
		}
		 catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return goods;
	}
  
	public List<Goods> getGoods(int start, int pagesize,String searchname) {
		// TODO Auto-generated method stub
		List<Goods> goods=new ArrayList<Goods>();
		Connection connection=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select * from goods where gname like ? limit ?,?");
		try {
			PreparedStatement pStatement=connection.prepareStatement(sql.toString());
			pStatement.setString(1, "%"+searchname+"%");
			pStatement.setInt(2, start);
			pStatement.setInt(3, pagesize);
			ResultSet rs=pStatement.executeQuery();
			while(rs.next()){
				Goods gs=new Goods();
				gs.setGid(rs.getInt("gid"));
				gs.setGname(rs.getString("gname"));
				gs.setGinventory(rs.getShort("ginventory"));
				gs.setGprice(rs.getDouble("gprice"));
				gs.setGdate(rs.getString("gdate"));
				gs.setGquality(rs.getString("gquality"));
				gs.setGaddress(rs.getString("gaddress"));
				gs.setGclassify(rs.getString("gclassify"));
				gs.setGcost(rs.getDouble("gcost"));
				goods.add(gs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return goods;
	}
   
   public int getgoodscount() {
   	// TODO Auto-generated method stub
	   int sgoods=0;
		Connection connection=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select count(gid) sgoods from goods ");
		try {
			PreparedStatement pStatement=connection.prepareStatement(sql.toString());
			ResultSet rs=pStatement.executeQuery();
			if (rs.next()) {
				sgoods=rs.getInt("sgoods");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sgoods;
   }

public int getgoodscount(String searchname) {
	// TODO Auto-generated method stub
	int sgoods=0;
	Connection connection=JDBCUtil.getConnection();
	StringBuffer sql=new StringBuffer("select count(gid) sgoods from goods where gname like ?");
	try {
		PreparedStatement pStatement=connection.prepareStatement(sql.toString());
		pStatement.setString(1, "%"+searchname+"%");
		ResultSet rs=pStatement.executeQuery();
		if (rs.next()) {
			sgoods=rs.getInt("sgoods");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return sgoods;
}


public boolean insertgoods(Goods g) {
	// TODO Auto-generated method stub
	boolean flag=false;
	Connection connection=JDBCUtil.getConnection();
	StringBuffer sql=new StringBuffer("insert into goods (gname,ginventory,gprice,gdate,gquality,gaddress,gclassify,gcost) values(?,?,?,?,?,?,?,?)");
	try {
		PreparedStatement pStatement=connection.prepareStatement(sql.toString());
		pStatement.setString(1, g.getGname());
		pStatement.setInt(2, g.getGinventory());
		pStatement.setDouble(3, g.getGprice());
		pStatement.setString(4, g.getGdate());
		pStatement.setString(5, g.getGquality());
		pStatement.setString(6, g.getGaddress());
		pStatement.setString(7, g.getGclassify());
		pStatement.setDouble(8, g.getGcost());
		int i=pStatement.executeUpdate();
		if (i>0) {
			flag=true;
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return flag;
}


public boolean deletegoods(String[] g) {
	// TODO Auto-generated method stub
	Connection con=null;
	PreparedStatement stmt=null;
	boolean flag=false;
	con=JDBCUtil.getConnection();
	StringBuffer sql=new StringBuffer("delete from goods where ");
	for(int i=0;i<g.length-1;i++){
		sql.append(" gid"+"="+"?"+"  or");
	}
	sql.append(" gid"+"="+"?");
	try {
		stmt=con.prepareStatement(sql.toString());
	} catch (SQLException e1) {
		// TODO Auto-generated catch block
		e1.printStackTrace();
	}
	for(int j=0;j<g.length;j++){
		try {
			
			stmt.setInt(j+1,Integer.parseInt(g[j]));
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
    try {
	int a=		stmt.executeUpdate();
	if(a>0){
		flag=true;
	}
   JDBCUtil.closeJDBC(stmt, con);
} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}
	return flag;
}


public boolean updategoods(Goods g) {
	// TODO Auto-generated method stub
	
	boolean flag=false;
	Connection connection=JDBCUtil.getConnection();
	StringBuffer sql=new StringBuffer("update goods set gname=?,ginventory=?,gprice=?,gdate=?,gquality=?,gaddress=?,gclassify=?,gcost=? where gid=?");
	try {
		PreparedStatement pStatement=connection.prepareStatement(sql.toString());
		pStatement.setInt(9, g.getGid());
		pStatement.setString(1, g.getGname());
		pStatement.setInt(2, g.getGinventory());
		pStatement.setDouble(3, g.getGprice());
		pStatement.setString(4, g.getGdate());
		pStatement.setString(5, g.getGquality());
		pStatement.setString(6, g.getGaddress());
		pStatement.setString(7, g.getGclassify());
		pStatement.setDouble(8, g.getGcost());
		int i=pStatement.executeUpdate();
		if (i>0) {
			flag=true;
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return flag;
}

public int selectMaxGid() {
	 int flag=0;
	Connection con=null;
	PreparedStatement statement=null;
	ResultSet rs=null;
	con=JDBCUtil.getConnection();
	StringBuffer sql=new StringBuffer("select max(gid) gid from goods");
	try {
		statement=con.prepareStatement(sql.toString());
	rs=	statement.executeQuery();
		if(rs.next()){
			flag=rs.getInt("gid");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return flag;
}

public Goods selectGoodsByGid(int gid) {
	 Connection con=null;
	 PreparedStatement stmt=null;
	 ResultSet rs=null;
	 Goods goods=new Goods();
	 
	 con=JDBCUtil.getConnection();
	 StringBuffer sql=new StringBuffer("select * from goods where gid=?");
	 try {
		stmt=con.prepareStatement(sql.toString());
		 stmt.setInt(1, gid);
		 rs=stmt.executeQuery();
		 if(rs.next()){
			 goods.setGid(rs.getInt("gid"));
			 goods.setGname(rs.getString("gname"));
			 goods.setGaddress(rs.getString("gaddress"));
			 goods.setGclassify(rs.getString("gclassify"));
			 goods.setGdate(rs.getString("gdate"));
			 goods.setGinventory(rs.getInt("ginventory"));
			 goods.setGprice(rs.getDouble("gprice"));
			 goods.setGquality(rs.getString("gquality"));
			 goods.setGcost(rs.getDouble("gcost"));
		 }
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	 
	 return goods;
}

public boolean updataGoods(int gid, int number) {
	 Connection con=null;
	 PreparedStatement stmt=null;
	 boolean flag=false;
	 
	 con=JDBCUtil.getConnection();
	 StringBuffer sql=new StringBuffer("update goods set ginventory=ginventory-? where gid=?");
	 try {
		stmt=con.prepareStatement(sql.toString());
		stmt.setInt(1, number);
		stmt.setInt(2, gid);
		int a=stmt.executeUpdate();
		if(a>0){
			flag=true;
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return flag;
}




}
